#!/bin/bash

#创建dwd层客户大宽表 dwd_tb_cust_info
hive -e "set hive.exec.mode.local.auto=true;create table if not exists daekeyijiance.dwd_tb_cust_info (cif_no string,cif_name string,cif_type string,local_flag string,crt_date date,data_date date);"

#dwd层客户大宽表最后的数据日期
max_date=$(hive -e "select coalesce(max(data_date), '1970-01-01') from daekeyijiance.dwd_tb_cust_info" 2>/dev/null)

#向客户大宽表中更新数据
hive -e "set hive.exec.mode.local.auto=true;insert into daekeyijiance.dwd_tb_cust_info (cif_no,cif_name,cif_type,local_flag,crt_date,data_date) select cif_no,cif_name,cif_type,local_flag,crt_date,data_date from daekeyijiance.ods_tb_cust_info where data_date > to_date('$max_date');"

#创建dwd层交易大宽表 dwd_tb_cust_tx
hive -e "set hive.exec.mode.local.auto=true;create table if not exists daekeyijiance.dwd_tb_cust_tx (trace_no string,cif_no string,cif_name string,cif_type string,opn_br_no string,tx_br_no string,ac_id string,ct_ind string,rece_pay_flag string,tx_amt double,tx_cny_amt double,tx_usd_amt double,tx_date date,tx_time timestamp,local_flag string,cb_flag string);"

#dwd层交易大宽表最后的数据日期
max_date_tx=$(hive -e "select coalesce(max(tx_date), '1970-01-01') from daekeyijiance.dwd_tb_cust_tx" 2>/dev/null)

#向交易大宽表中更新数据
hive -e "set hive.exec.mode.local.auto=true;insert into daekeyijiance.dwd_tb_cust_tx (trace_no,cif_no,cif_name,cif_type,opn_br_no,tx_br_no,ac_id,ct_ind,rece_pay_flag,tx_amt,tx_cny_amt,tx_usd_amt,tx_date,tx_time,local_flag,cb_flag) select trace_no,cif_no,cif_name,cif_type,opn_br_no,tx_br_no,ac_id,ct_ind,rece_pay_flag,tx_amt,tx_cny_amt,tx_usd_amt,tx_date,tx_time,local_flag,cb_flag from daekeyijiance.ods_tb_cust_tx where tx_date > to_date('$max_date_tx');"
